



use ZCMS
GO
--删除外建
select 'ALTER TABLE ' + b.name + ' DROP CONSTRAINT ' + a.name +';'  from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name='表名'; 
--删除表数据
select 'TRUNCATE TABLE ' + a.name  +';'  from sysobjects a  where a.xtype ='U' ; 
--删除表
select 'DROP TABLE ' + a.name  +';'  from sysobjects a  where a.xtype ='U' ; 

------------------------------------------------------




use ZCMS
GO
declare @sql varchar(8000)
while (select count(*) from sysobjects where type='U')>0
begin
SELECT @sql='drop table ' + name
FROM sysobjects
WHERE (type = 'U')
ORDER BY 'drop table ' + name
exec(@sql) 
end

-------------------------------------------------------------------


USE ZCMS
GO
select @n=1
insert #temp(tablename) SELECT distinct sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'

select @n=1
select @rows = @@rowcount
while @n <= @rows
begin
 
    declare @tablename nvarchar(50)
    select @tablename = (select tablename from #temp where id = @n)
    --允许将显式值插入表的标识列中 ON-允许  OFF-不允许
    --exec ('set identity_insert '+@tablename+' ON')--打开
    exec ('ALTER TABLE  '+@tablename+' NOCHECK constraint all')
    print('alert table '+@tablename)
    select @n = @n + 1
end

select @n=1
select @rows = @@rowcount
while @n <= @rows
begin
 
    declare @tablename nvarchar(50)
    select @tablename = (select tablename from #temp where id = @n)
    --允许将显式值插入表的标识列中 ON-允许  OFF-不允许
    --exec ('set identity_insert '+@tablename+' ON')--打开
    exec ('TRUNCATE TABLE  '+@tablename)
    print('drop table '+@tablename)
    select @n = @n + 1
end
